<?php defined('SYSPATH') or die('No direct script access.');

/**
*待收货预约单
*/
class Controller_Unreceipt extends Controller_Admin{
    /**
     * 权限
     */
    public function before(){
        try{
            ACL::Required('select_unreceipt');
        }catch(HTTP_Exception_403 $e){
            Message::error('sorry，你没有权限进这个目录');
            $this->request->redirect(URL::base(TRUE));
        }

        parent::before();
    }

    /**
     * 待收货预约单查询
     */
    public function Action_unreceipt(){
        $this->title = '待收货预约单查询';
        $this->response->body(View::factory('receipt/unreceipt'));
    }

    /**
     * 根据SKU等查询条件查找出预约单号
     */
    public function Action_sku_select($flag = true){
        $this->title = '待收货预约单查询';
        $view = View::factory('receipt/unreceipt');
        $view->operate_man = isset($_GET['operate_man'])?trim($_GET['operate_man']):NULL;
        $view->supplier_id = isset($_GET['supplier_id'])?trim($_GET['supplier_id']):NULL;
        $view->supplier_name = isset($_GET['supplier_name'])?trim($_GET['supplier_name']):NULL;
        $view->sku = isset($_GET['sku'])?trim($_GET['sku']):NULL;
        $view->date_from_c = isset($_GET['date_from_c'])?trim($_GET['date_from_c']):NULL;
        $view->date_to_c = isset($_GET['date_to_c'])?trim($_GET['date_to_c']):NULL;
        $view->merchandiser = isset($_GET['merchandiser'])?trim($_GET['merchandiser']):null;
        if(count($_GET)){
            $supplier_ids = '';
            $sql = 'SELECT p.*,pd.*,pd.status as pod_status,p.status as po_status
                 FROM purchaseorders p JOIN purchaseorder_details pd ON p.id=pd.purchaseorder_id
                 WHERE pd.status>=20 and pd.status<70 and p.status>=20 and p.status<70';
            $view->sku && $sql .=" and pd.sku='".$view->sku."'";

            //计划员
            if($view->operate_man){
                $userId = null;
                $userId = DB::query(Database::SELECT,
                    "SELECT `id` FROM `users` WHERE nick like '%{$view->operate_man}%'")->execute('newerp')->as_array();
                $userId = count($userId)?'('.implode(',',array_column($userId,'id')).')':'(0)';
                $userId && $sql .=" and p.purchaseplaner in {$userId}";
            }
            
            //查询供应商表的ID
/*             if($view->supplier_name){
                $supplier = basic::getidByName($view->supplier_name);
                $orms->where('purchaseorders.supplier_id','=',$supplier);
            } */
            if(!$view->supplier_id && $view->supplier_name){
                Message::error('你查找的内容没有记录');
            }
            $view->supplier_id && $sql .= " and p.supplier_id=".$view->supplier_id;
            //跟单员
            $view->merchandiser && $sql .= " and pd.merchandiser like '%".$view->merchandiser."%'";
            //处理时间
            $view->date_from_c && $sql .= " and p.purchase_time>'".$view->date_from_c."'";
            $view->date_to_c && $sql .= " and p.purchase_time<'".$view->date_to_c." 23:59:59'";

            $result_all = DB::query(Database::SELECT,$sql)
                ->execute('newerp')
                ->as_array();
            $count = count($result_all);

            if($count == 0){
                Message::error('你查找的内容没有记录');
            }else{
                $pagination = Pagination::factory(array(
                    'total_items' => $count,
                    //总记录数
                    'items_per_page' => 20,
                    //每页显示多少条
                    'uri' => $this->request->uri(),
                ));
                if($flag==false){
                    $sql .= " order by p.purchase_time desc ";
                }else{
                    $sql .= " order by p.purchase_time desc limit ".$pagination->items_per_page." offset ".$pagination->_offset;
                }

                $sku_select = DB::query(Database::SELECT,$sql)
                    ->execute('newerp')
                    ->as_array();
                foreach($sku_select as $key => $value){
                    $ordertype = $value['createtype'];
                    if($ordertype == 1){
                        $sku_select[$key]['createtype'] = '手动创建';
                    }elseif($ordertype == 2){
                        $sku_select[$key]['createtype'] = '预警生成';
                    }else{
                        $sku_select[$key]['createtype'] = '未知';
                    }
                    $supplier_ids .= empty($supplier_ids)?$value['supplier_id']:','.$value['supplier_id'];
                    //获取订单明细表的状态
                    $pdetail_status = basic::get_podetail_status();
                    $sku_select[$key]['pod_status']
                        = (isset($pdetail_status[$value['pod_status']]))?$pdetail_status[$value['pod_status']]:'';
                }
                if($flag==false){
                    return $sku_select;
                }
                $page_links = $pagination->render();//渲染分页到模板
                $view->page_links = $page_links;
                $view->sku_select = $sku_select;
/*                 $suppliers=DB::query(Database::SELECT,'SELECT `id`,`name` FROM `skusystem_suppliers` WHERE `id` IN ('.$supplier_ids.')')
                    ->execute('newerp')
                    ->as_array('id','name');
                $view->suppliers = $suppliers; */
            }
        }

        $this->response->body($view);
    }

    /**
     * 待收货的预约单报表下载
     */
    public function Action_export(){
//        $result_all = $this->getData();
        $result_all = $this->Action_sku_select(false);
        $purchaser_ids = array();
        $supplier_ids = array();
        foreach($result_all as &$result_one){
            if(!in_array($result_one['purchase_id'],$purchaser_ids)){
                $purchaser_ids[] = $result_one['purchase_id'];
            }
            if(!in_array($result_one['supplier_id'],$supplier_ids)){
                $supplier_ids[] = $result_one['supplier_id'];
            }

        }
        $getPurchaserNameSql = 'SELECT `id`,`nick` FROM `users` WHERE `id` IN ('.implode(',',$purchaser_ids).')';
        $purchaserName = DB::query(Database::SELECT,$getPurchaserNameSql)
            ->execute()
            ->as_array('id','nick');
        $getSupplierNameSql = 'SELECT `supplier_id`,`supplier_name` FROM `vmi_supplier_skus` WHERE `supplier_id` IN ('.implode(',',$supplier_ids).') group by supplier_id';
        $supplierName = DB::query(Database::SELECT,$getSupplierNameSql)
            ->execute()
            ->as_array('supplier_id','supplier_name');
        foreach($result_all as &$result_one){
            $result_one['purchaser_name'] = $purchaserName[$result_one['purchase_id']];
            $result_one['supplier_name'] = $supplierName[$result_one['supplier_id']];
        }
        $resultNames = array(
            "purchaseorder_id" => "VMI预约单号",
            "sku" => "SKU",
            "sku_name" => "中文名称",
            "sku_standard"=>"规格",
            "storage_position" => "储位",
            "supplier_name" => "供应商",
            "purchaser_name" => "计划员",
            "merchandiser" => "跟单员",
            "quantity" => "采购数量",
            "purchase_time" => "预约单时间",
//            "pstatus" => "预约单状态",
//            "pdid" => "预约单明细号",
            "pod_status" => "明细单状态",
            "recieve_quantity" => "已收货",
            "ware_quantity" => "已入库",
            "return_quantity" => "退回",
            "remark" => "备注",
        );
        $content = $this->createXLSTable($resultNames,$result_all);
        header("Content-type:application/vnd.ms-execl;charset=gb2312");
        header("Content-Disposition:attactment;filename=待收货的预约单.xls");
        header("Pragma: no-cache");
        header("Expires: 0");
        echo $content;
        exit;
    }

    /**
     * 获取前台提交数据进行相关数据查询的操作
     * @author chamlinsun@qq.com 2017-11-24
     */
    static protected function getData($page = NULL){
        $sql = 'SELECT p.id pid,p.purchase_time,pd.sku_standard,p.purchase_id,p.supplier_id,ss.supplier_name,p.status pstatusid,
            pd.id pdid,pd.sku,pd.sku_name,pd.storage_position,pd.quantity,pd.recieve_quantity,pd.status pdstatusid,pd.remark,
            pd.return_quantity,pd.merchandiser
            FROM `purchaseorders` p
            LEFT JOIN `purchaseorder_details` pd            
            ON  p.id=pd.purchaseorder_id
            LEFT  JOIN vmi_supplier_skus ss ON ss.supplier_id = p.supplier_id
            WHERE p.status<70
            AND p.status>=20
            AND pd.status<70
            AND pd.status>=20 ';//20已打印，70完全入库，获取未完结采购信息
        if(isset($_GET)&&!empty($_GET['sku'])){
            $sql .= ' AND pd.sku="'.$_GET['sku'].'"';
        }
        //跟单员
        $merchandiser = isset($_GET['merchandiser'])?trim($_GET['merchandiser']):NULL;
        $merchandiser && $sql .=" and pd.merchandiser like '%".$merchandiser."%'";
        //计划员
        $operate_man = isset($_GET['operate_man'])?trim($_GET['operate_man']):NULL;
        if($operate_man){
            $userId = null;
            $userId = DB::query(Database::SELECT,
                "SELECT `id` FROM `users` WHERE nick like '%{$operate_man}%'")->execute('newerp')->as_array();
            $userId = count($userId)?'('.implode(',',array_column($userId,'id')).')':'(0)';
            $userId && $sql .=" and p.purchaseplaner in {$userId}";
        }
        $supplier_id = isset($_GET['supplier_id'])?trim($_GET['supplier_id']):NULL;
        $supplier_id && $sql .=" and p.supplier_id=".$supplier_id;

        //处理时间
        $date_from_c = isset($_GET['date_from_c'])?trim($_GET['date_from_c']):NULL;
        $date_to_c = isset($_GET['date_to_c'])?trim($_GET['date_to_c']):NULL;
        $date_from_c && $sql .=" and p.purchase_time>'".$date_from_c."'";
        $date_to_c && $sql .=" and p.purchase_time<'".$date_to_c." 23:59:59'";
        $sql .= ' order by p.purchase_time desc';
        $result_all = DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->as_array();
        if(count($result_all)==0){
            return NULL;
        }

        //获得采购员名称
        $purchaser_ids = '';
        foreach($result_all as $result_one){
            $purchaser_ids .= empty($purchaser_ids)?$result_one['purchase_id']:','.$result_one['purchase_id'];
        }
        $getPurchaserNameSql = 'SELECT `id`,`nick` FROM `users` WHERE `id` IN ('.$purchaser_ids.')';
        $purchaserName = DB::query(Database::SELECT,$getPurchaserNameSql)
            ->execute()
            ->as_array('id','nick');
        $sql = 'SELECT `number`,`value`
            FROM statusdics
            WHERE `table`="purchaseorders"
            AND `colum_name`="status"';
        $pstatus = DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->as_array('number','value');
        $sql  = 'SELECT `number`,`value`
            FROM statusdics
            WHERE `table`="purchaseorder_details"
            AND `colum_name`="status"';
        $pdstatus = DB::query(Database::SELECT,$sql)
            ->execute('newerp')
            ->as_array('number','value');
        foreach($result_all as &$result_one){
            $result_one['purchaser_name'] = $purchaserName[$result_one['purchase_id']];
            $result_one['pstatus'] = $pstatus[$result_one['pstatusid']];
            $result_one['pdstatus'] = $pdstatus[$result_one['pdstatusid']];
        }

        return $result_all;
    }

    /**
     *根据列明和已获得的相关信息数组生成table类型的字符串输出，用于后面进行excel表格生成
     */
    static protected function createXLSTable($columnNames,$datas){
        $keys = array_keys($columnNames);
        $content = "";
        $content .= "<table border='1'><tr>";
        foreach($columnNames as $key => $columnName){
            $content .= "<td>$columnName</td>";
        }
        $content .= "</tr>";
        foreach($datas as $key => $data){
            $content .= "<tr>";
            foreach($keys as $key){
                $content .= "<td>".$data[$key]."</td>";
            }
            $content .= "</tr>";
        }
        $content .= "</table>";
        
        return $content;
    }
}
